package com.xs.tool.common.sqllite;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.List;
import java.util.Properties;

/**
 * sqllite 数据库工具类
 *
 * @author xs
 * @date 20210121
 */
public class SqlLiteUtil {
    private static Logger logger = LoggerFactory.getLogger(SqlLiteUtil.class);
    private static String SQL_LITE_PAY = "xs.db3";//数据库路径
    private static final String PROPERTIES_FILE_NAME = "xs.properties";//配置文件地址
    private static final String XS_SQLLITE_KEY = "xs.sqlite.url";//企业客户号

    private static String SQLITE_URL = "jdbc:sqlite:" + SQL_LITE_PAY;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    // 数据库版本
    private static final int DB_VERSION = 3;

    private static Properties prop = getProperties();
    private static SqlLiteUtil instance = null;

    public static SqlLiteUtil getInstance() {
        if (instance == null) {
            synchronized (SqlLiteUtil.class) {
                if (instance == null) {
                    instance = new SqlLiteUtil();
                }
            }
        }
        return instance;
    }

    /**
     * 获取配置文件
     *
     * @return
     */
    private static Properties getProperties() {
        Properties prop = new Properties();
        InputStream resourceAsStream = null;
        InputStreamReader inputStreamReader = null;
        try {
            //方案一
            //读取文件流
//            FileInputStream file = new FileInputStream(PROPERTIES_FILE_URL);
//            prop.load(file);
            // 方案二
            //读取文件流
            resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(PROPERTIES_FILE_NAME);
            //转变为字符流
            inputStreamReader = new InputStreamReader(resourceAsStream, "utf-8");
//            prop.load(new InputStreamReader(in, "utf-8"));
            //加载字符流
            prop.load(inputStreamReader);
//            file.close();
        } catch (Exception e) {
            logger.error("配置文件读取失败 Failed to read configuration file msg={}", e.getMessage());
        } finally {
            try {
                if (null != resourceAsStream) {
                    resourceAsStream.close();
                }
                if (null != inputStreamReader) {
                    inputStreamReader.close();
                }
            } catch (Exception e) {
                logger.error("io 异常");
            }
        }
        return prop;
    }

    static {
        try {
            String sqlLiteUrl = prop.getProperty(XS_SQLLITE_KEY);
            if (null != sqlLiteUrl && !sqlLiteUrl.equals("")) {
                SQL_LITE_PAY = sqlLiteUrl;
            } else {
                logger.debug("xs.properties no property  xs.sqlite.url");
            }
            //驱动
            Class.forName("org.sqlite.JDBC");
        } catch (Exception e) {
            logger.error("reade xs.properties error msg={}", e.getMessage());
        }
    }

    public SqlLiteUtil() {
        try {
            Connection temp = DriverManager.getConnection(SQLITE_URL);
            threadLocal.set(temp);
        } catch (Exception e) {
            logger.error("init error msg ={}", e.getMessage());
        }
    }

    /**
     * 获取连接
     *
     * @return
     */
    public Connection getConnection() {
        try {
            Connection connection = threadLocal.get();
            boolean isNeedCreate = false;
            if (connection == null) {
                isNeedCreate = true;
            } else {
                try {
                    if (connection.isClosed()) {
                        isNeedCreate = true;
                    }
                } catch (SQLException e1) {
                    isNeedCreate = true;
                }
            }
            if (isNeedCreate) {
                Connection temp = null;
                try {
                    temp = DriverManager.getConnection(SQLITE_URL);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                threadLocal.set(temp);
                return temp;
            } else {
                return connection;
            }
        } catch (Exception e) {
            logger.error("getConnection error msg={}", e.getMessage());
        }
        return null;
    }

    /**
     * 释放连接对应线程的连接
     */
    public void close() {
        try {
            Connection connection = threadLocal.get();
            threadLocal.remove();
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            logger.error("close error msg={}", e.getMessage());
        }
    }

    /**
     * 返回 PreparedStatement 对象，用于放置参数,无返回值
     *
     * @param sql
     * @return
     */
    public void excuteSql(String sql, SqlTemplate sqlTemplate) {
        Connection connection = getConnection();
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            sqlTemplate.setPreparedStatement(stmt);
            // 最主要是就是这2行
            stmt.addBatch();
            // 执行
            stmt.executeBatch();
            // 提交
            connection.commit();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            logger.error("excuteSql error msg={},sql={}", e.getMessage(),sql);
            System.exit(0);
        } finally {
            close();
        }
    }

    /**
     * 返回用于执行大量的sql语句
     *
     * @param sql          单个sql模板
     * @param sqlTemplates 执行方式的集合
     */
    public void excuteSqlMany(String sql, List<SqlTemplate> sqlTemplates) {
        Connection c = getConnection();
        try {
            c.setAutoCommit(false);
            PreparedStatement stmt = c.prepareStatement(sql);
            for (int i = 0; i < sqlTemplates.size(); i++) {
                SqlTemplate sqlTemplate = sqlTemplates.get(i);
                sqlTemplate.setPreparedStatement(stmt);
                // 最主要是就是这2行
                stmt.addBatch();
                // 执行
                stmt.executeBatch();
            }
            // 提交
            c.commit();
            stmt.close();
            c.close();
        } catch (Exception e) {
            logger.error("excuteSqlMany error msg={},sql={}", e.getMessage(),sql);
            System.exit(0);
        }
    }

    /**
     * 主要用于查询操作
     *
     * @param sql
     * @param sqlTemplate
     */
    public <E> List<E> excuteSqlAndGetData(String sql, SqlTemplate sqlTemplate, Class<E> clazz) {
        Connection c = getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = c.prepareStatement(sql);
            sqlTemplate.setPreparedStatement(stmt);
            ResultSet resultSet = stmt.executeQuery();
            List<E> objects = ResultSetMapper.mapRersultSetToObject(resultSet, clazz);
            return objects;
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(0);
        } finally {
            try {
                stmt.close();
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 通过这个接口获取PreparedStatement实例，然后放置对应的参数
     */
    public static interface SqlTemplate {
        /**
         * 在刚方法内部需要通过 preparedStatement 放置参数
         *
         * @param preparedStatement
         */
        void setPreparedStatement(PreparedStatement preparedStatement);
    }
}
